package com.soda.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.soda.bean.News;
import com.soda.bean.User;
import com.soda.dao.BaseDao;
import com.soda.dao.NewsDao;
import com.soda.util.PageInfo;
import com.soda.util.SelectNewsInfo;

public class NewsDaoImpl extends BaseDao implements NewsDao {

	// 增加
	public int addNews(News n) {
		try {
			getConnection();
			String sql = "INSERT INTO news VALUES(default,?,?,NOW(),?,?,?,1)";
			pt = conn.prepareStatement(sql);
			pt.setString(1, n.getNewAuthor());
			pt.setString(2, n.getNewTitle());
			pt.setInt(3, n.getNewCount());
			pt.setInt(4, n.getNewState());
			pt.setString(5, n.getNewContent());
			return pt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return 0;
	}

	// 全查
	public News showAllNews(int id) {
		// TODO Auto-generated method stub
		try {
			getConnection();
			String sql = "select * from news where new_id=?";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, id);
			res = pt.executeQuery();
			if (res.next()) {
				News n = new News(res.getInt(1), res.getString(2),
						res.getString(3), res.getString(4), res.getInt(5),
						res.getInt(6), res.getString(7), res.getInt(8));

				return n;

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}

		return null;
	}

	public List<News> showNews(int id) {
		// TODO Auto-generated method stub
		try {
			getConnection();
			String sql = "select * from news where new_id=?";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, id);
			List<News>list=new ArrayList<News>();
			res = pt.executeQuery();
			if (res.next()) {
				News n = new News(res.getInt(1), res.getString(2),
						res.getString(3), res.getString(4), res.getInt(5),
						res.getInt(6), res.getString(7), res.getInt(8));

				list.add(n);
			}
			return list;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}

		return null;
	}
	// 条件查询
	public List<News> getAllNews(SelectNewsInfo sni, PageInfo pi) {
		// , PageInfo pi
		try {
			getConnection();
			StringBuffer sql = new StringBuffer(
					"select * from news where new_delete=1");
			if (sni != null) {
				if (sni.getNewtitle() != null
						|| sni.getNewtitle().length() != 0) {
					sql.append(" and new_title like ?");
				}
				if (sni.getUpdatetime() != null
						|| sni.getUpdatetime().length() != 0) {
					sql.append(" and new_time like ?");
				}

				if (sni.getState() == 1 || sni.getState() == 0) {
					sql.append(" and new_state=?");
				}

			}
			sql.append(" order by new_id desc limit ?,? ");
			pt = conn.prepareStatement(sql.toString());
			int x = 1;
			if (sni != null) {
				if (sni.getNewtitle() != null
						|| sni.getNewtitle().length() != 0) {
					pt.setString(x, sni.getNewtitle() + "%");
					x++;
				}
				if (sni.getUpdatetime() != null
						|| sni.getUpdatetime().length() != 0) {
					pt.setString(x, sni.getUpdatetime() + "%");
					x++;
				}
				if (sni.getState() >= 0) {
					if (sni.getState() == 0 || sni.getState() == 1) {
						pt.setInt(x, sni.getState());
						x++;
					}
				}
			}
			// 分页信息
			pt.setInt(x, (pi.getPage() - 1) * pi.getPer());
			x++;
			pt.setInt(x, pi.getPer());
			List<News> list = new ArrayList<News>();
			res = pt.executeQuery();
			while (res.next()) {
				News n = new News();
				n.setNewId(res.getInt(1));
				n.setNewAuthor(res.getString(2));
				n.setNewTitle(res.getString(3));
				n.setNewTime(res.getString(4));
				n.setNewCount(res.getInt(5));
				n.setNewState(res.getInt(6));
				n.setNewContent(res.getString(7));
				n.setNewDelete(res.getInt(8));
				list.add(n);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;
	}

	public int getNewsCount(SelectNewsInfo sni) {
		// TODO Auto-generated method stub
		try {
			getConnection();
			StringBuffer sql = new StringBuffer(
					"select count(1) from news where new_delete=1");
			if (sni != null) {
				if (sni.getNewtitle() != null
						|| sni.getNewtitle().length() != 0) {
					sql.append(" and new_title like ?");
				}
				if (sni.getUpdatetime() != null
						|| sni.getUpdatetime().length() != 0) {
					sql.append(" and new_time like ?");
				}

				if (sni.getState() == 1 || sni.getState() == 0) {
					sql.append(" and new_state=?");
				}

			}
			pt = conn.prepareStatement(sql.toString());
			int x = 1;
			if (sni != null) {
				if (sni.getNewtitle() != null
						|| sni.getNewtitle().length() != 0) {
					pt.setString(x, sni.getNewtitle() + "%");
					x++;
				}
				if (sni.getUpdatetime() != null
						|| sni.getUpdatetime().length() != 0) {
					pt.setString(x, sni.getUpdatetime() + "%");
					x++;
				}
				if (sni.getState() >= 0) {
					if (sni.getState() == 0 || sni.getState() == 1) {
						pt.setInt(x, sni.getState());
						x++;
					}
				}
			}
			res = pt.executeQuery();
			if (res.next()) {
				return res.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return 0;
	}

	
	public List<News> selectNews(PageInfo pi) {
		try {
			getConnection();
			String sql = "select * from news limit ?,?";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, (pi.getPage() - 1) * pi.getPer());
			pt.setInt(2, pi.getPer());
			res = pt.executeQuery();
			List<News> list = new ArrayList<News>();
			while (res.next()) {
				News n = new News(res.getInt(1), res.getString(2),
						res.getString(3), res.getString(4), res.getInt(5),
						res.getInt(6), res.getString(7), res.getInt(8));
				list.add(n);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;
	}

	// 单删
	public int deleteById(int id) {

		try {
			getConnection();
			String sql = "UPDATE news SET new_delete=0 WHERE new_id=?";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, id);
			return pt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();

		}
		return 0;
	}

	// 批量删除
	public int delAllNews(String[]list) {
		try {
			getConnection();
			StringBuffer sql = new StringBuffer("update news set new_delete=0 where new_id in(");
			 for(int i=0;i<list.length;i++){
				 sql.append("?,");
			 }
			sql.deleteCharAt(sql.length() - 1);
			sql.append(")");

			pt = conn.prepareStatement(sql.toString());
			int x=1;
			for(int i=0;i<list.length;i++){
				pt.setInt(x,Integer.parseInt(list[i]));
				x++;
			}
			return pt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}

		return 0;
	}
	
	
	
	// 修改

	public int updateNews(int id, int recommend, String neirong) {

		try {
			getConnection();
			String sql = "UPDATE news SET new_state=?,new_content=?,new_time=NOW() WHERE new_id=?";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, recommend);
			pt.setString(2, neirong);
			pt.setInt(3, id);
			return pt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}

		return 0;
	}
	//导出表单
	
	
		public List<News> exportNews(String[] count) {
			try{
			getConnection();
			StringBuffer sql=new StringBuffer("select * from news where new_id in(");
			for (int i = 0; i < count.length; i++) {
				sql.append("?,");
			}
			sql.deleteCharAt(sql.length()-1);
			sql.append(")");
			pt=conn.prepareStatement(sql.toString());
			int x=1;
			for (int i = 0; i < count.length; i++) {
				pt.setInt(x, Integer.parseInt(count[i]));
				x++;
			}
			res=pt.executeQuery();
			List<News> list=new ArrayList<News>();
			while(res.next()){
				News news=new News(res.getInt(1), res.getString(2), res.getString(3), res.getString(4), res.getInt(5), res.getInt(6), res.getString(7), res.getInt(8));
				
				list.add(news);
				
			}
			return list;
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close();
		}	
			return null;

		}
//修改状态
		public int updateStateNews(int newId, int state) {
			// TODO Auto-generated method stub
			try {
				getConnection();
				String sql="update news set new_state=? where new_id=?";
				pt=conn.prepareStatement(sql);
				pt.setInt(1, state);
				pt.setInt(2,newId);
				return pt.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			return 0;
		}


	

}
